package pe.gob.osinergmin.interfase_siaf.dac.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import pe.gob.osinergmin.interfase_siaf.dac.common.dao.impl.Conexion;
import pe.gob.osinergmin.interfase_siaf.dac.dao.ExpedienteSecuenciaDao;
import pe.gob.osinergmin.interfase_siaf.dac.domain.ExpedienteSecuencia;

@Repository("expedienteSecuenciaDao")
public class ExpedienteSecuenciaDaoImpl implements ExpedienteSecuenciaDao {


	  @Value("${jdbc.username}")
	  private String userName;

	  @Value("${jdbc.password}")
	  private String password;	 
	  
	  @Value("${jdbc.driverClassNameODBC}")
	  private String driverClassNameODBC;
	  
	  @Value("${jdbc.urlDataODBC}")
	  private String urlODBC;
	  
	  private final Log logger = LogFactory.getLog(getClass());
	
	
		 //private NamedParameterJdbcTemplate jdbcParameterTemplateData;	
		// private JdbcTemplate jdbcTemplateData;	
		 
		/* @Autowired
		 public void SetDataSource(DataSource dataSourceData){
			//this.jdbcTemplateData = new JdbcTemplate(dataSourceData);
			//this.jdbcParameterTemplateData = new NamedParameterJdbcTemplate(dataSourceData);

		 }
        */
		 
		 /**DECLARACION DE SQL**/
				 
		 private static final String SQL_SELECT_EXPGASTO_MONTO =
					/*" SELECT SUM(monto) AS monto FROM expediente_secuencia WHERE expediente=:expediente AND "
					+ " secuencia=:secuencia AND fase=:fase AND correlativ=:correlativ AND ano_eje =:anoEje AND ciclo =:ciclo ";*/
		            " SELECT SUM(monto) AS monto FROM expediente_secuencia WHERE ano_eje =?  AND sec_ejec=? AND "
			        + " expediente=? AND ciclo =? AND fase=? AND secuencia=?  AND correlativo=? ";
		 
		 private static final String SQL_SELECT_EXPGASTO_MONTO_MN =
					/*" SELECT SUM(monto_naci) AS monto_naci FROM expediente_secuencia WHERE expediente=:expediente AND "
					+ " secuencia=:secuencia AND fase=:fase AND correlativ=:correlativ AND ano_eje =:anoEje AND ciclo =:ciclo ";*/
		            " SELECT SUM(monto_nacional) AS monto_naci FROM expediente_secuencia WHERE ano_eje =?  AND sec_ejec=? AND "
	                + " expediente=? AND ciclo =? AND fase=? AND secuencia=?  AND correlativo=? ";

		 
		 private static final String SQL_COUNT_EXP_GASTO_ODBC =
				 " SELECT COUNT(expediente) AS N1 FROM expediente_secuencia WHERE ano_eje =?  AND sec_ejec=? AND "
				+ " expediente=? AND ciclo =? AND fase=? AND secuencia=?  AND correlativo=? ";
		 
	    /**IMPLEMENTACION DE METODOS**/
		 		 
		// @Override
	   /*  public List<ExpedienteClasif> bucarMontoTotalCompromiso1(ExpedienteClasif exp) throws Exception {		 
				SqlParameterSource parameters = new BeanPropertySqlParameterSource(exp);
				RowMapper<ExpedienteClasif> lista = ParameterizedBeanPropertyRowMapper.newInstance(ExpedienteClasif.class);		
			    return this.jdbcParameterTemplateData.query(SQL_SELECT_EXPGASTO_MONTO, parameters, lista);	
	    }
		 */
		//@Override
	   /* public List<ExpedienteClasif> bucarMontoMnTotalCompromiso1(ExpedienteClasif exp) throws Exception {		 
				SqlParameterSource parameters = new BeanPropertySqlParameterSource(exp);
				RowMapper<ExpedienteClasif> lista = ParameterizedBeanPropertyRowMapper.newInstance(ExpedienteClasif.class);		
			    return this.jdbcParameterTemplateData.query(SQL_SELECT_EXPGASTO_MONTO_MN, parameters, lista);	
	    } */
		
		////////////////////////////////////////////////////////////////////////////////////////	 
		 @Override		
	     public int verificarExiteExpeGasto(ExpedienteSecuencia exp) throws Exception{
			 int valorExiste =0;
			 try {
				 logger.info("Entrando a la consulta con  odbc existe expediente  ");				
				 Connection conn =  Conexion.getConexion(driverClassNameODBC,urlODBC,userName,password);				
				 PreparedStatement pst = conn.prepareStatement(SQL_COUNT_EXP_GASTO_ODBC);
				 pst.setString(1, exp.getAnoEje());
				 pst.setString(2, exp.getSecEjec());
				 pst.setString(3, exp.getExpediente());
				 pst.setString(4, exp.getCiclo());
				 pst.setString(5, exp.getFase());
				 pst.setString(6, exp.getSecuencia());
				 pst.setString(7, exp.getCorrelativo());				 
				 ResultSet rs = pst.executeQuery();
      	         while (rs.next()) {
      	        	logger.info("encontro valores");      	        	
      	        	valorExiste = rs.getInt("N1");
      	        	logger.info("valorExiste :   "+valorExiste);
      	         }	  
      	          rs.close();
			      pst.close();
			      conn.close();
			    } catch (Exception e) {
			    	valorExiste=0;
				  e.printStackTrace();
			    }
			 return valorExiste;
		 }
		 
		 @Override		
	     public double bucarMontoTotalCompromiso(ExpedienteSecuencia exp) throws Exception{
			 double monto = 0.0;
			 try {
				 logger.info("Entrando a la consulta con  odbc monto   ");				
				 Connection conn =  Conexion.getConexion(driverClassNameODBC,urlODBC,userName,password);				
				 PreparedStatement pst = conn.prepareStatement(SQL_SELECT_EXPGASTO_MONTO);
				 pst.setString(1, exp.getAnoEje());
				 pst.setString(2, exp.getSecEjec());
				 pst.setString(3, exp.getExpediente());
				 pst.setString(4, exp.getCiclo());
				 pst.setString(5, exp.getFase());
				 pst.setString(6, exp.getSecuencia());
				 pst.setString(7, exp.getCorrelativo());				 
				 ResultSet rs = pst.executeQuery();
      	         while (rs.next()) {
      	        	logger.info("encontro valor monto");      	        	
      	        	monto = rs.getDouble("monto");
      	        	logger.info("monto :   "+monto);
      	         }	  
      	          rs.close();
			      pst.close();
			      conn.close();
			    } catch (Exception e) {
			    	monto=0.0;
				  e.printStackTrace();
			    }
			 return monto;
		 }
		 
		 @Override		
	     public double bucarMontoMnTotalCompromiso(ExpedienteSecuencia exp) throws Exception{
			 double montoMn =0.0;
			 try {
				 logger.info("Entrando a la consulta con  odbc monto mn   ");				
				 Connection conn =  Conexion.getConexion(driverClassNameODBC,urlODBC,userName,password);				
				 PreparedStatement pst = conn.prepareStatement(SQL_SELECT_EXPGASTO_MONTO_MN);
				 pst.setString(1, exp.getAnoEje());
				 pst.setString(2, exp.getSecEjec());
				 pst.setString(3, exp.getExpediente());
				 pst.setString(4, exp.getCiclo());
				 pst.setString(5, exp.getFase());
				 pst.setString(6, exp.getSecuencia());
				 pst.setString(7, exp.getCorrelativo());				 
				 ResultSet rs = pst.executeQuery();
      	         while (rs.next()) {
      	        	logger.info("encontro valor monto mn");      	        	
      	        	montoMn = rs.getDouble("monto_naci");
      	        	logger.info("monto mn :   "+montoMn);
      	         }	  
      	          rs.close();
			      pst.close();
			      conn.close();
			    } catch (Exception e) {
			    	montoMn= 0.0;
				  e.printStackTrace();
			    }
			 return montoMn;
		 }

		/**
		 * @return the userName
		 */
		public String getUserName() {
			return userName;
		}

		/**
		 * @param userName the userName to set
		 */
		public void setUserName(String userName) {
			this.userName = userName;
		}

		/**
		 * @return the password
		 */
		public String getPassword() {
			return password;
		}

		/**
		 * @param password the password to set
		 */
		public void setPassword(String password) {
			this.password = password;
		}

		/**
		 * @return the driverClassNameODBC
		 */
		public String getDriverClassNameODBC() {
			return driverClassNameODBC;
		}

		/**
		 * @param driverClassNameODBC the driverClassNameODBC to set
		 */
		public void setDriverClassNameODBC(String driverClassNameODBC) {
			this.driverClassNameODBC = driverClassNameODBC;
		}

		/**
		 * @return the urlODBC
		 */
		public String getUrlODBC() {
			return urlODBC;
		}

		/**
		 * @param urlODBC the urlODBC to set
		 */
		public void setUrlODBC(String urlODBC) {
			this.urlODBC = urlODBC;
		}

	
		 
		 
		 
	
}
